<?php 
$req_path="../";
require_once($req_path."includes/db.php");

class M_Employee extends DB{
    private $rowCount=0;
    public $table_name="tb_employee";

	
    function M_Employee(){
		parent::__construct();
    }
	
	function getMonth(){
		$sql = "SELECT DATE_FORMAT(value,'%m/%Y') as value 
				FROM tb_system_config WHERE name = 'SALARY_MONTH'" ;
				
		$result = $this->ExecuteData($sql);
		return $result->FetchRow();
	}
	function getNextMonth(){
		$sql="SELECT DATE_FORMAT(DATE_ADD(value,INTERVAL 1 month),'%m/%Y') as value 
				, DATE_FORMAT(DATE_ADD(value,INTERVAL 1 month),'%Y/%m/%d') as NextMonth
				, DATE_FORMAT(value,'%Y/%m/%d') as CurrMonth
				FROM tb_system_config WHERE name = 'SALARY_MONTH'";
		$result = $this->ExecuteData($sql);
		return $result->FetchRow();
	}
	function updateNextMonth(){
		$sql="UPDATE tb_system_config set value=DATE_ADD(value,INTERVAL 1 month) 
			 WHERE name = 'SALARY_MONTH'";
		$result = $this->ExecuteData($sql);
		return $result->FetchRow();
	}
    function loadMonth(){
		$sql = "SELECT DATE_FORMAT(value,'%m/%Y')  value
				FROM tb_system_config WHERE name = 'SALARY_MONTH'
				UNION  
				SELECT Date_FORMAT( month ,'%m/%Y') curr_month 
				FROM tb_employee_salary
				group by  Date_FORMAT( month ,'%m/%Y') 
				ORDER BY value desc " ;
		$result = $this->ExecuteData($sql);
		 
		while($row = $result->FetchRow()){
			$ar[$row["value"]] = $row["value"];
		}
		return $ar;	
	}
	function loadNotice($name, $df, $dt,$n_id=0){
		$sql = " SELECT msg.id, date_format(notice_date, '%d/%m/%Y') noticedate, detail , msg.title
				, '' title_by , em.name
				, case when em1.title='0' then 'นาย'  when em1.title='1' then 'นางสาว' else 'นาง' end titled , em1.name as nameed
				FROM tb_message msg
				LEFT JOIN tb_user em on em.id = msg.notice_by
				LEFT JOIN tb_employee em1 on em1.id= msg.noticed
				WHERE msg.del_flage='N' ";
				
		if ($df!=''){
			list($d,$m,$y) = explode('/',$df);
			list($d1,$m1,$y1) = explode('/', $dt);
			$date = $y.'/'.$m.'/'.$d;
			$date1=$y1.'/'.$m1.'/'.$d1;
			$sql.= " AND DATE_FORMAT(notice_date,'%Y/%m/%d') BETWEEN '".$date."' and '".$date1."'";
		}
		if ($name !='' ){
			$sql.= " and em1.name like '%".$name."%'";
		}
		if ($n_id !=0){
			$sql.=" AND noticed = ".$n_id;
		}
		$sql .=" ORDER BY notice_date desc ";
		//print_r($sql);
        $result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	
	}
	function checkCode($code){
		$sql = " SELECT * FROM tb_employee WHERE code ='".$code."'"  ;
		$result = $this->ExecuteData($sql);
        return  $result->RecordCount();
	}
    function loadData($code='',$name='', $did=0,$sid=0, $month="",$id=""){
        
        $sqlscript= "SELECT u.id, u.id_card, u.code,u.name,case when title='0' then 'นาย'  when title='1' then 'นางสาว' else 'นาง' end title , DATE_FORMAT(u.date_in,'%d/%m/%Y') as datein,d.name as departmentname,s.name statusname
					,address, telephone ,u.education , u.did, u.status , u.title as title_id , u.account ";
		$sqlscript.= " , u.updateby, user.name as username , DATE_FORMAT(ifnull(u.updatedate,'') , '%d/%m/%Y') as update_date , u.salary ,course_fee ";
		$sqlscript.= " FROM tb_employee u ";
		$sqlscript.= " LEFT JOIN tb_user user on user.id = u.updateby ";
		$sqlscript.= " LEFT JOIN tb_department d on d.id = u.did ";
		$sqlscript.= " LEFT JOIN tb_status_employee s on s.id = u.status ";
		$sqlscript.= " LEFT JOIN tb_employee_salary es on es.employee_id = u.id and DATE_FORMAT(es.month,'%m/%Y') ='".$month."'";
		$sqlscript.= " WHERE 1=1 ";
		if ($name !=''){
			$sqlscript.= " and u.name like '%".$name."%'" ;
		}
		if ($code !=''){
			$sqlscript.= " and u.code = '".$code."'" ;
		}
		if ($sid !=0){
			$sqlscript.= " and u.status = ".$sid ;
		}
		if ($did!=0){
			$sqlscript.= " and u.did = ".$did ;
		}
		if ($id !=""){
			$sqlscript.= " and u.id_card = '".$id."'";
		}
		$sqlscript.=" order by u.code ";
      // print_r($sqlscript);
        $result = $this->ExecuteData($sqlscript);
        $this->rowCount = $result->RecordCount();
        return $result;
    }
	
	 function loadDataDetail($id=0,$month=''){
        
        $sqlscript= "SELECT u.id, u.id_card, u.code,u.name,case when title='0' then 'นาย'  when title='1' then 'นางสาว' else 'นาง' end title 
					,d.name as departmentname ,ifnull(u.salary,0) as salary  
					 , ifnull(u.salary,0) + ifnull(es.other_income,0) - ifnull( es.tax , 0) -  ifnull( es.fund , 0) - ifnull( es.other_outcome , 0) as grand_total
				 	, ifnull(es.id,0) as emp_id";
		$sqlscript.= " FROM tb_employee u ";
		$sqlscript.= " LEFT JOIN tb_department d on d.id = u.did ";
		$sqlscript.=" LEFT JOIN tb_employee_salary es on es.employee_id = u.id and DATE_FORMAT( es.month ,'%m/%Y') = '".$month."'";
 
		$sqlscript.= " WHERE 1=1 and u.id=".$id;
		 
       
        $result = $this->ExecuteData($sqlscript);
        $this->rowCount = $result->RecordCount();
        return $result->FetchRow();
    }
	function loadIncome($id=0,$month=''){
		$sql = "SELECT es.employee_id, es.month, es.id, es.salary
				, inlist.name , sd.amount, sd.emp_salary_id , inlist.delete_flage
				, sd.o_id
				FROM tb_employee_salary es
				LEFT JOIN tb_salary_detail sd on sd.emp_salary_id = es.id and sd.type ='A'
				LEFT JOIN tb_income_list inlist on inlist.id = sd.o_id  
				WHERE 1=1 " ;

		
		if ($id!=0){
			$sql.=" AND es.employee_id =".$id;
		}
		if ($month !=''){
			$sql .=" AND DATE_FORMAT( es.month ,'%m/%Y') = '".$month."'"; 
		}
		 //print_r($sql);
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	
	function loadOutcome($id=0,$month=''){
		$sql = "SELECT es.employee_id, es.month, es.id, es.salary
				, inlist.name , sd.amount, sd.emp_salary_id
				, sd.o_id
				FROM tb_employee_salary es
				LEFT JOIN tb_salary_detail sd on sd.emp_salary_id = es.id 
				LEFT JOIN tb_outcome_list inlist on inlist.id = sd.o_id  
				WHERE 1=1 and sd.type ='D' " ;
	
		if ($id!=0){
			$sql.=" AND es.employee_id =".$id;
		}
		if ($month !=''){
			$sql .=" AND DATE_FORMAT( es.month ,'%m/%Y') = '".$month."'"; 
		}
		
		$result = $this->ExecuteData($sql);
        $this->rowCount = $result->RecordCount();
        return $result;
	}
	function DeleteSalaryDetail($id){
		$sql = "DELETE FROM tb_salary_detail WHERE emp_salary_id = ".$id;
		$this->ExecuteData($sql);
	}
	function updateEmployeeSalary($id, $salary, $tax , $fund , $other_in , $other_out, $user_id){
		$sql = " UPDATE tb_employee_salary SET salary=".$salary.",other_income=".$other_in." , other_outcome=". $other_out." , tax=".$tax." , fund=".$fund.",updatedate=now(), updateby=".$user_id." WHERE id = ".$id;
		$this->ExecuteData($sql);
	}
	function InsertSalaryDetail($ar){
		$sqlInsert=$this->InsertSqlScript(" SELECT emp_salary_id, o_id, amount , type  FROM tb_salary_detail where 1=2 " , $ar);
		$this->ExecuteData($sqlInsert);
	}
	function InsertEmployeeSalary($ar){
		$sql = " SELECT month, employee_id, salary, total_in , total_out ,createdate, createby FROM tb_employee_salary WHERE 1=2";
		$sqlInsert=$this->InsertSqlScript($sql , $ar);
		$this->ExecuteData($sqlInsert);
		return $this->getLastID();
	}
    function getRowCount(){
        
        return $this->rowCount;
        
    }	
	function getUserStatus($type=''){
	 
		$sqlscript= "select id, name from tb_status_employee where 1=1 ";
		if($type!=''){
			$sqlscript .= " and status = '".$type."' ";
		}
		$sqlscript .=" Order by id asc ";
		
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	function getDepartment($status=''){
		$sqlscript= "select id, name from tb_department where 1=1 ";
		if($status!=''){
			$sqlscript .= " and status = '".$status."' ";
		}
		$sqlscript .=" Order by id asc ";
		
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["name"]] = $row["id"];
		}
		return $ar;	
	}
	function getIncome(){
		$sqlscript= "select id, name from tb_income_list where status='Y' and delete_flage='Y' ";
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["id"]] = $row["name"];
		}
		return $ar;	
	}
	function getEmployee(){
		$sql = " SELECT em.id, case when em.title='0' then 'นาย'  when em.title='1' then 'นางสาว' else 'นาง' end title_by , em.name
				 FROM   tb_employee em
				 WHERE status = 3
				 ORDER BY em.name asc ";
				 
		$result = $this->ExecuteData($sql);
		while($row = $result->FetchRow()){
			//$ar[$row["id"]] = $row['title_by']. ' '. $row["name"];
			$ar[$row['title_by']. ' '. $row["name"]] = $row['id'];
		}
		return $ar;	
	}
	function getOutcome(){
		$sqlscript= "select id, name from tb_outcome_list where status='Y' ";
		$result = $this->ExecuteData($sqlscript);
		while($row = $result->FetchRow()){
			$ar[$row["id"]] = $row["name"];
		}
		return $ar;	
	}
    function insertData($val){
            if (isset($val)){
				$sqlInsert=$this->InsertSqlScript("SELECT id_card,salary,account,code,name,course_fee,title,date_in, did, education, status,telephone, address,createdate,createby FROM ". $this->table_name." where 1=2 ", $val);
				return $this->ExecuteDataAndGetID($sqlInsert);
            }
    }
	function addNotice($val){
		$sql= " SELECT notice_date, title, detail , notice_by, noticed FROM tb_message where 1=2 " ;
		$sqlInsert=$this->InsertSqlScript($sql, $val);
		$this->ExecuteData($sqlInsert);
	}
	function deleNotice($id){
		$sql = " UPDATE tb_message SET del_flage = 'Y' WHERE id = ".$id;
		$this->ExecuteData($sql);
	}
    function updateData($id,$val){
            if(isset($val)){
				$sqlUpdate=$this->UpdateSqlScript("SELECT  id_card,salary,account,code,name,title,date_in,course_fee, did,telephone, education,status, updatedate,updateby  FROM ". $this->table_name." where id='".$id."'" ,$val);
				 $this->ExecuteData($sqlUpdate);
				// return $sqlUpdate;
            }
    }
	function updateSalary($id,$salary=0,$user_id){
		$_next_month=$this->getNextMonth();
		
		$sql="UPDATE tb_employee_salary SET salary=".$salary.", updatedate=now(), updateby=".$user_id." WHERE employee_id=".$id." and DATE_FORMAT(month,'%Y/%m/%d')='".$_next_month['CurrMonth']."' ";
		$this->ExecuteData($sql);
		//print_r($sql);
		$sql= " SELECT s.emp_salary_id
				 FROM tb_employee_salary e
				 JOIN tb_salary_detail s on s.emp_salary_id = e.id
				 WHERE DATE_FORMAT(e.month,'%Y/%m/%d') ='".$_next_month['CurrMonth']."' 
				 and s.o_id=1 and s.`type`= 'A' and e.employee_id=".$id;  
				 
		$result=$this->ExecuteData($sql);		 
		//print_r($sql);
		$cnt = $result->RecordCount();
		if ($cnt > 0){
			$row=$result->FetchRow();
			$sql="UPDATE  tb_salary_detail SET amount=".$salary." WHERE o_id=1 and `type`= 'A' and emp_salary_id=".$row['emp_salary_id'];
			$this->ExecuteData($sql);
			//print_r($sql);
		}else{
		
			$sql= " INSERT INTO tb_salary_detail(emp_salary_id,o_id,amount,type)
					SELECT id, 1 , salary , 'A'
					FROM tb_employee_salary
					WHERE DATE_FORMAT(month,'%Y/%m/%d') ='".$_next_month['CurrMonth']."' and employee_id=".$id;
		 
			$this->ExecuteData($sql);
			//print_r($sql);
		}
	}
    function deleData($id){
            if(isset($id)){
			$sqlDelete = "Delete FROM ". $this->table_name." where id='".$id."'";
			$this->ExecuteData($sqlDelete);
			return 1;
            }
    }
	function AddSalary($user_id){
		$_next_month=$this->getNextMonth();
		
		$sql="INSERT INTO tb_employee_salary(month,employee_id,salary,createdate, createby)
				 SELECT '".$_next_month['NextMonth']."', id as user_id , salary ,now(),".$user_id."
				 FROM tb_employee WHERE status=3 ";
		 
		$this->ExecuteData($sql);
		
		$sql= " INSERT INTO tb_salary_detail(emp_salary_id,o_id,amount,type)
				 SELECT id, 1 , salary , 'A'
				 FROM tb_employee_salary
				 WHERE DATE_FORMAT(month,'%Y/%m/%d') ='".$_next_month['NextMonth']."'";
		 
		$this->ExecuteData($sql);
		
		$this->updateNextMonth();
	}
	function AddSalaryById($id, $user_id){
		$_next_month=$this->getNextMonth();
		
		$sql="INSERT INTO tb_employee_salary(month,employee_id,salary,createdate, createby)
				 SELECT '".$_next_month['CurrMonth']."', id as user_id , salary ,now(),".$user_id."
				 FROM tb_employee WHERE status=3 and id=".$id;
		 
		$this->ExecuteData($sql);
		
		$sql= " INSERT INTO tb_salary_detail(emp_salary_id,o_id,amount,type)
				 SELECT id, 1 , salary , 'A'
				 FROM tb_employee_salary
				 WHERE DATE_FORMAT(month,'%Y/%m/%d') ='".$_next_month['CurrMonth']."' and employee_id=".$id;
		 
		$this->ExecuteData($sql);
		
		//$this->updateNextMonth();
	}
    
}
?>
